package com.smart.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


import com.smart.model.Notice;
import com.smart.util.DBUtil;

public class NoticeDAO {

	public void addNotice(Notice notice) throws SQLException
	{
		 Connection conn=DBUtil.getConnection();
		 String sql="insert into notice(community_id, summary, title, content) values(?,?,?,?);";
		 PreparedStatement pSt=conn.prepareCall(sql);
		 pSt.setInt(1, notice.getCommunityId());
		 pSt.setString(2, notice.getSummary());
		 pSt.setString(3, notice.getTitle());
		 pSt.setString(4, notice.getContent());

		 pSt.execute();
	}
	
	public Notice findNotice(int id) throws SQLException
	{
		 Connection conn=DBUtil.getConnection();
		 String sql = "select * from notice where id=?";
		 PreparedStatement pSt = conn.prepareCall(sql);
		 pSt.setInt(1, id);
		 ResultSet res = pSt.executeQuery();
		 Notice ntc = new Notice();
		 if(res.next())
		 {
		  ntc.setId(res.getInt("id"));
		  ntc.setTitle(res.getString("title"));
		  ntc.setContent(res.getString("content"));
		  System.out.println("content:"+ntc.getContent());
		  ntc.setCreateTime(res.getDate("create_time").toString());
		 }
		 return ntc;
	}
	
	public List<Notice> listNotice(int id) throws SQLException
	{
		Connection conn=DBUtil.getConnection();
		String sql="select * from  notice where id<? order by id desc limit 0,10;";
		PreparedStatement pSt=conn.prepareCall(sql);
		pSt.setInt(1, id);
		ResultSet res=pSt.executeQuery();
		List<Notice> list = new ArrayList<Notice>();
		while(res.next())
		{
		    Notice ntc = new Notice();
		    ntc.setId(res.getInt("id"));
		    ntc.setTitle(res.getString("title"));
		    ntc.setSummary(res.getString("summary"));
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String createTime=sdf.format(res.getTimestamp("create_time"));
			ntc.setCreateTime(createTime);
			list.add(ntc);
		 }
		 return list;
	}
}
